The following CP Duplicate Record Check Formula and Duplicate Record Check Formula specify a duplicate match key using the first three digits/characters of the zip code, the first four characters of the last name (if one exists), the first character of the first name, or if the customer did not have a last name, the first five characters of the company name:
CP Duplicate Record Check Formula
UPPER(SUBSTRING(Name.ZIP,1,3) + (CASE WHEN Name.LAST_NAME<>'' THEN (SUBSTRING(Name.LAST_NAME,1,4) + SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5) END))
Duplicate Record Check Formula
upp(con(mid(Name.ZIP,1,3),pick(Name.LAST_NAME<>'',mid(Name.COMPANY,1,5),con(mid(Name.LAST_NAME,1,4),mid(Name.FIRST_NAME,1,1)))))
For customer Diane Block with an address of 500 Main Street, Austin, Texas 78708, the following duplicate match key would be created:
787BLOCD
10.6 Production Release. Updated 1/20/2006 12:52:05 PM
© Copyright by Advanced Solutions International, Inc.
All rights reserved.